#  单元措施经济评价计算
#  tb_year_dvpunit_oilstimulate_evaluate_result表
import os
from fastapi.encoders import jsonable_encoder

import xlwt
from fastapi import APIRouter, Form
from starlette.requests import Request
import xlrd
from backend.app.app.api.calculation.unit_calculate import welloprcost, grosssalesincome, totaltax, staticgains, \
    stimnpv, stimroi, stimnpvr, stimfirr, increoilcritical, oilpricecritical
from backend.app.app.models.evaluate_calculate import Oil_Sale_TotalTax, Oil_Opr_Cost, Output_Page_Data, \
    Save_table_data, Save_Data_Private, User_Proj_page
from backend.app.app.crud_pro.tb_calculate import get_alldata, table_store, table_store_private, \
    save_page1_data, save_page2_data, save_page3_data
from starlette.templating import Jinja2Templates

templates = Jinja2Templates(directory="templates")
calculate_router = APIRouter(prefix="/eva_cul", tags=["单元措施经济评价计算"])


# 打开excel表
def open_excel(file_name):
    workbook = xlrd.open_workbook(file_name)

    names = workbook.sheet_names()
    print(names)

    worksheet = workbook.sheet_by_index(0)
    print(worksheet)

    name = worksheet.name  # 获取表的姓名
    print(name)

    nrows = worksheet.nrows  # 获取该表总行数
    print(nrows)

    ncols = worksheet.ncols  # 获取该表总列数
    print(ncols)

    sheet0_name = workbook.sheet_names()[0]  # 通过sheet索引获取sheet名称
    print(sheet0_name)

    for i in range(nrows):  # 循环打印每一行
        print(worksheet.row_values(i))


# 存储excel表
def store_excel():
    book = xlwt.Workbook(encoding="utf-8")
    sheet = book.add_sheet('test1', cell_overwrite_ok=True)
    # 其中的test是这张表的名字,cell_overwrite_ok，表示是否可以覆盖单元格，其实是Worksheet实例化的一个参数，默认值是False
    sheet.write(0, 0, '1')  # 其中的'0-行, 0-列'指定表中的单元
    sheet.write(0, 1, '2')
    # 添加第二个表
    # sheet2 = book.add_sheet("test02", cell_overwrite_ok=True)
    a = ['0', '0', '0', '0', '0']
    b = ['5047.4', '3247.9', '1514.7', '1374.3']
    c = ['1', '1', '1']

    # 填入第一列
    for i in range(0, len(a)):
        sheet.write(i + 1, 0, a[i])

    # 填入第二列
    for i in range(0, len(b)):
        sheet.write(i + 1, 1, b[i])

    # 填入第一行
    for i in range(0, len(c)):
        sheet.write(0, i, c[i])

    # 说明这个表还未创建
    num = 0
    for m in range(100):
        if os.path.exists('../test{}.xls'.format(m)) is False:  # 说明这个表还未创建
            break
        num = m + 1

    book.save('C:/Users/Desktop/test{}.xls'.format(num))


@calculate_router.post("/main", name="表格初始界面")
async def eva_cul_main():
    result_data = get_alldata()

    return result_data


@calculate_router.post("/output_page_data", name="输出数据界面表格界面")  # 此时是填入数据并计算后的界面
async def output_data_page(output_page_data: Output_Page_Data):
    # 平均增油操作成本
    WellOprCost = welloprcost(output_page_data.increoil, output_page_data.oiloprcost)
    # 平均增油毛收入
    GrossSalesIncome = grosssalesincome(output_page_data.increoil, output_page_data.oilprice,
                                        output_page_data.oilcommodityratio)
    # 平均销售税费
    TotalTax = totaltax(GrossSalesIncome, output_page_data.totaltaxratio)
    # 平均静态收益
    StaticGains = staticgains(GrossSalesIncome, output_page_data.stimcost, WellOprCost, TotalTax)
    # 平均财务净现值
    StimNpv = stimnpv(output_page_data.increoil, output_page_data.validperiod, output_page_data.oilcommodityratio,
                      output_page_data.oilprice, output_page_data.totaltaxratio, output_page_data.oiloprcost,
                      output_page_data.discount, output_page_data.stimcost)
    # 投资利润率
    StimROI = stimroi(StaticGains, output_page_data.stimcost)
    # 财务净现值率
    StimNPVRV = stimnpvr(StimNpv, output_page_data.stimcost)
    # 财务内部收益率
    StimFirr = stimfirr(StaticGains, output_page_data.stimcost, output_page_data.validperiod)
    # 平均经济临界增油量
    IncreOilCritical = increoilcritical(output_page_data.validperiod, output_page_data.stimcost,
                                        output_page_data.discount, output_page_data.oilprice,
                                        output_page_data.oilcommodityratio, output_page_data.totaltaxratio,
                                        output_page_data.oiloprcost)
    # 平均经济临界油价
    OilPriceCritical = oilpricecritical(output_page_data.discount, output_page_data.validperiod,
                                        output_page_data.stimcost, output_page_data.increoil,
                                        output_page_data.oiloprcost,
                                        output_page_data.oilcommodityratio, output_page_data.totaltaxratio)

    return WellOprCost, GrossSalesIncome, TotalTax, StaticGains, StimNpv, StimROI, StimNPVRV, StimFirr, IncreOilCritical, OilPriceCritical


@calculate_router.post("/save_data", name="存储数据界面")
async def output_data_page(save_table_data: Save_table_data, save_data_private: Save_Data_Private):
    try:
        table_store(dvpunit_name=save_table_data.DvpUnit_Name,
                    year=save_table_data.Year,
                    stimtype_name=save_table_data.StimType_Name,
                    stim_wellnumber_valid=save_table_data.Stim_WellNumber_Valid,
                    stim_wellvalidratio=save_table_data.Stim_WellValidRatio,
                    stim_validperiod_average=save_table_data.Stim_ValidPeriod_Average,
                    stim_increoil_average=save_table_data.Stim_IncreOil_Average,
                    stimcost_average=save_table_data.StimCost_Average,
                    oil_oprcost=save_table_data.Oil_OprCost,
                    oil_price=save_table_data.Oil_Price,
                    stimoil_commodityratio=save_table_data.StimOil_CommodityRatio,
                    oilsale_totaltaxratio=save_table_data.OilSale_TotalTaxRatio,
                    money_discount=save_table_data.Money_Discount,
                    remark=save_table_data.Remark,
                    stimoil_welloprcost_average=save_table_data.StimOil_WellOprCost_Average,
                    stimoil_total_taxratio_average=save_table_data.StimOil_Total_TaxRatio_Average,
                    stimoil_gross_salesincome_average=save_table_data.StimOil_Gross_SalesIncome_Average,
                    stimoil_static_gains_average=save_table_data.StimOil_Static_Gains_Average,
                    stimoil_npv_average=save_table_data.StimOil_NPV_Average,
                    stimoil_roi=save_table_data.StimOil_ROI,
                    stimoil_npvr=save_table_data.StimOil_NPVR,
                    stimoil_firr=save_table_data.StimOil_FIRR,
                    stim_increoil_critical_average=save_table_data.Stim_IncreOil_Critical_Average,
                    stimoil_price_critical=save_table_data.StimOil_Price_Critical)

        table_store_private(user_id=save_data_private.User_ID,
                            proj_id=save_data_private.Proj_ID,
                            dvpunit_name=save_data_private.DvpUnit_Name,
                            year=save_data_private.Year,
                            stimtype_name=save_data_private.StimType_Name,
                            stim_wellnumber_valid=save_data_private.Stim_WellNumber_Valid,
                            stim_wellvalidratio=save_data_private.Stim_WellValidRatio,
                            stim_validperiod_average=save_data_private.Stim_ValidPeriod_Average,
                            stim_increoil_average=save_data_private.Stim_IncreOil_Average,
                            stimcost_average=save_data_private.StimCost_Average,
                            oil_oprcost=save_data_private.Oil_OprCost,
                            oil_price=save_data_private.Oil_Price,
                            stimoil_commodityratio=save_data_private.StimOil_CommodityRatio,
                            oilsale_totaltaxratio=save_data_private.OilSale_TotalTaxRatio,
                            money_discount=save_data_private.Money_Discount,
                            remark=save_data_private.Remark,
                            stimoil_welloprcost_average=save_data_private.StimOil_WellOprCost_Average,
                            stimoil_total_tax_average=save_data_private.StimOil_Total_TaxRatio_Average,
                            stimoil_gross_salesincome_average=save_data_private.StimOil_Gross_SalesIncome_Average,
                            stimoil_static_gains_average=save_data_private.StimOil_Static_Gains_Average,
                            stimoil_npv_average=save_data_private.StimOil_NPV_Average,
                            stimoil_roi=save_data_private.StimOil_ROI,
                            stimoil_npvr=save_data_private.StimOil_NPVR,
                            stimoil_firr=save_data_private.StimOil_FIRR,
                            stim_increoil_critical_average=save_data_private.Stim_IncreOil_Critical_Average,
                            stimoil_price_critical=save_data_private.StimOil_Price_Critical)
        print('储存成功')
        return True
    except:
        return False


# 存储用户第一界面数据及设置信息
@calculate_router.post("/user_proj_page", name="用户第一页面数据")
async def proj_page1(user_proj_page: User_Proj_page, page_data=None, filter_data=None, set_info=None, picture1=None,
                    picture2=None, picture3=None):
    try:
        save_page1_data(user_id=user_proj_page.User_ID,
                        proj_id=user_proj_page.Proj_ID,
                        remake=user_proj_page.Ramake,
                        page_data=page_data,
                        filter_data=filter_data,
                        set_info=set_info,
                        picture1=picture1,
                        picture2=picture2,
                        picture3=picture3
                        )
        return True
    except:
        return False


# 存储用户第二界面数据及设置信息
@calculate_router.post("/user_proj_page", name="用户第二页面数据")
async def proj_page2(user_proj_page: User_Proj_page, page_data=None, filter_data=None, set_info=None, picture1=None,
                    picture2=None, picture3=None):
    try:
        save_page2_data(user_id=user_proj_page.User_ID,
                        proj_id=user_proj_page.Proj_ID,
                        remake=user_proj_page.Ramake,
                        page_data=page_data,
                        filter_data=filter_data,
                        set_info=set_info,
                        picture1=picture1,
                        picture2=picture2,
                        picture3=picture3
                        )
        return True
    except:
        return False


# 存储用户第三界面数据及设置信息
@calculate_router.post("/user_proj_page", name="用户第三页面数据")
async def proj_page3(user_proj_page: User_Proj_page, page_data=None, filter_data=None, set_info=None, picture1=None,
                    picture2=None, picture3=None):
    try:
        save_page3_data(user_id=user_proj_page.User_ID,
                        proj_id=user_proj_page.Proj_ID,
                        remake=user_proj_page.Ramake,
                        page_data=page_data,
                        filter_data=filter_data,
                        set_info=set_info,
                        picture1=picture1,
                        picture2=picture2,
                        picture3=picture3
                        )
        return True
    except:
        return False
